﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;
using System.Xml.Serialization;

namespace qna.Business {
	[DataObject]
	public class Question : BusinessObject {
		[DataObjectField(true, true)]
		public int Id { get; set; }

		[DataObjectField(false, false)]
		public string Title { get; set; }

		[DataObjectField(false, false)]
		public string Content { get; set; }

		[DataObjectField(false, false)]
		public DateTime DateTimeUpdated { get; set; }

		[DataObjectField(false, false)]
		public int UserId { get; set; }

		[DataObjectField(false, false)]
		public int SubjectId { get; set; }

		[DataObjectField(false, false)]
		public int AnswerId { get; set; }


		public int Rating { get; set; }
		public int Answers { get; set; }
		public string UserName {
			get { return user == null ? "" : user.Name; }
			set { user.Name = value; }
		}
		private User user;

		protected override string TableName {
			get { return "question"; }
		}

		protected override BusinessObject New() {
			return new Question();
		}

		public override IDictionary<string, object> Attributes {
			get {
				IDictionary<string, object> attrs = new Dictionary<string, object>();
				attrs["Id"] = Id;
				attrs["Title"] = Title;
				attrs["Content"] = Content;
				attrs["Datetime_Updated"] = DateTimeUpdated;
				attrs["User_Id"] = UserId;
				attrs["Subject_Id"] = SubjectId;
				attrs["Answer_Id"] = AnswerId;
				return attrs;
			}
		}

		public override void ReadAttributes(System.Data.SqlClient.SqlDataReader dr) {
			Id = (int)dr["Id"];
			Title = dr["Title"].ToString();
			Content = dr["Content"].ToString();
			DateTimeUpdated = (DateTime)dr["Datetime_Updated"];
			UserId = (int)dr["User_Id"];
			SubjectId = (int)dr["Subject_Id"];
			AnswerId = dr["Answer_Id"] == DBNull.Value ? 0 : (int)dr["Answer_Id"];
		}

		[DataObjectMethod(DataObjectMethodType.Select, false)]
		public IList<Question> GetBySubject(int[] subjects, DateTime from, int rating = 0, int limit = 10) {
			IDictionary<string, object> param = new Dictionary<string, object>();
			//limit
			string query = "SELECT TOP " + limit;
			query += " * FROM " + TableName + " WHERE ";
			//subjects
			if (subjects.Length != 0) {
				query += " subject_id in (";
				foreach (int s in subjects) {
					query += s + ", ";
				}
				//remove comma
				query = query.Substring(0, query.Length - 2);
				query += ")";
			}
			//date
			if (from != null) {
				if (subjects.Length != 0)
					query += " AND ";
				query += " datetime_updated < @from ";
				param.Add("@from", from);
			}
			//rating
			query += " AND (SELECT COUNT(*) FROM question_rating "
			+ " WHERE question_id = " + PrimaryKey + ") >= @rating ";
			param.Add("rating", rating);

			query += " ORDER BY datetime_updated desc";

			List<BusinessObject> list = (List<BusinessObject>)QueryAll(query, param);
			AttachData(list);

			return list.ConvertAll<Question>(delegate(BusinessObject bo) {
				return (Question)bo;
			});
		}


        [DataObjectMethod(DataObjectMethodType.Insert, true)]
		public override int Insert(IDictionary<string, object> attrs, bool ignorePk = true) {
			attrs["Datetime_Updated"] = DateTimeUpdated = DateTime.Now;
			return base.Insert(attrs, ignorePk);
		}

		public IList<Question> Search(string searchText) {
			IDictionary<string, object> param = new Dictionary<string, object>();
			string sql = "SELECT * FROM " + TableName + " WHERE title LIKE '%" + searchText + "%' OR [content] LIKE '%" + searchText + "%'";

			List<BusinessObject> list = (List<BusinessObject>)QueryAll(sql, param);
			return list.ConvertAll<Question>(delegate(BusinessObject bo) {
				return (Question)bo;
			});
		}

		/// <summary>
		/// Method to select an answer to a question
		/// </summary>
		/// <param name="aID">ID of Answer to be the answer to the question</param>
		/// <param name="qID">ID of Question to be answered</param>
		/// <param name="uID">ID of User who posed the question</param>
		public bool SelectAnswer(int aID, int qID, int uID) {
			bool returnVal = false;

			//If the user was the creator of the question, update the answer.
			if (CheckSelectAnswer(uID, qID) == true) {
                Question q = (Question)GetById(qID);
                
				IDictionary<string, object> answerPar = new Dictionary<string, object>();
				string sql = "UPDATE question SET answer_id = @ansID WHERE id = @qID";
                if (q.AnswerId == aID)
                {
                    answerPar.Add("@ansID", DBNull.Value);
                }
                else
                {
                    answerPar.Add("@ansID", aID);
                }
				answerPar.Add("@qID", qID);
				int selectRowsAffected = ExecuteNonQuery(sql, answerPar);
				if (selectRowsAffected > 0)
					returnVal = true;
                    
				else
					returnVal = false;

				return returnVal;

			} else {
				returnVal = false;
			}

			return returnVal;
		}

		/// <summary>
		/// Method to check if the user was the creator of the question
		/// </summary>
		/// <param name="qID">ID of Question to be answered</param>
		/// <param name="uID">ID of user who is rating</param>
		public bool CheckSelectAnswer(int uID, int qID) {
			bool returnVal = false;

			IDictionary<string, object> questionPar = new Dictionary<string, object>();
			string sql = "SELECT COUNT(user_id) FROM Question WHERE user_id = @uid AND id = @qid";
			questionPar.Add("@uid", uID);
			questionPar.Add("@qid", qID);

			int selectRowsAffected = ExecuteScalar(sql, questionPar);
			if (selectRowsAffected > 0)
				returnVal = true;
			else
				returnVal = false;

			return returnVal;
		}

		/// <summary>
		/// Method to set the rating for Questions
		/// </summary>
		/// <param name="qID">ID of answer to rate</param>
		/// <param name="uID">ID of user who is rating</param>
		public bool SetQuestionRating(int qID, int uID) {
			bool returnVal = false;
			IDictionary<string, object> questionPar = new Dictionary<string, object>();

			string sqlInsert = "INSERT INTO question_rating VALUES(@question_id, @user_id)";
			string sqlDelete = "DELETE FROM question_rating WHERE question_id = @question_id AND user_id = @user_id";
			questionPar.Add("@question_id", qID);
			questionPar.Add("@user_id", uID);
			bool userRated = CheckIfUserRated(qID, uID);
			if (userRated == true) {
				int deleteRowsAffected = ExecuteNonQuery(sqlDelete, questionPar);
				if (deleteRowsAffected > 0)
					returnVal = true;
				else
					returnVal = false;
			} else {
				int insertRowsAffected = ExecuteNonQuery(sqlInsert, questionPar);

				if (insertRowsAffected > 0)
					returnVal = true;
				else
					returnVal = false;
			}

			return returnVal;
		}

		/// <summary>
		/// Method to get the total rating for an Answer
		/// </summary>
		/// <param name="qID">ID of answer to get rating of</param>
		public int GetQuestionRating(int qID) {
			IDictionary<string, object> questionPar = new Dictionary<string, object>();
			string sql = "SELECT COUNT(question_id) FROM question_rating WHERE question_id = @question_id";
			questionPar.Add("@question_id", qID);

			int totalRating = ExecuteScalar(sql, questionPar);
			return totalRating;
		}

		/// <summary>
		/// Method to check if the user has rated the Question
		/// </summary>
		/// <param name="qID">ID of Question to be rated</param>
		/// <param name="uID">ID of User who is rating</param>
		public bool CheckIfUserRated(int qID, int uID) {
			bool returnVal = false;

			IDictionary<string, object> questionPar = new Dictionary<string, object>();
			string sqlSelect = "SELECT COUNT(user_id) FROM question_rating WHERE question_id = @question_id AND user_id = @user_id";
			questionPar.Add("@question_id", qID);
			questionPar.Add("@user_id", uID);
			int selectRowsAffected = ExecuteScalar(sqlSelect, questionPar);
			if (selectRowsAffected > 0)
				returnVal = true;
			else
				returnVal = false;

			return returnVal;
		}

		public override BusinessObject GetById(int id) {
			Question q = (Question)base.GetById(id);
			var list = new List<BusinessObject>();
			list.Add(q);
			AttachData(list);
			return q;
		}

		private void AttachData(IList<BusinessObject> list) {
			if (list.Count != 0) {
				int[] ids = new int[list.Count];
				for (int i = 0; i < list.Count; i++) {

                        ids[i] = ((Question)list[i]).UserId;
				}

				//get users
				var users = (new User()).GetAllById(ids);
				foreach (Question q in list) {
					foreach (User u in users) {
						if (q.UserId == u.Id) {
							q.user = u;
							break;
						}
					}
					q.Answers = q.CountAnswers();
					q.Rating = q.GetQuestionRating(q.Id);
				}
			}
		}

		public override IList<BusinessObject> GetAll(int limit = 0) {
			var list = base.GetAll(limit);
			AttachData(list);
			return list;
		}

		public int CountAnswers() {
			string sql = "Select count(*) from answer WHERE question_id = @id";
			var param = new Dictionary<string, object>();
			param.Add("id", Id);
			return ExecuteScalar(sql, param);
		}
	}
}